package com.zhucai.credit.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.PaymentOrderItemEntity;

/**
 * 
 * @author hsg
 * PaymentOrderItem
 */
@Repository
public interface PaymentOrderItemRepository extends
		PagingAndSortingRepository<PaymentOrderItemEntity, Long>,
		JpaSpecificationExecutor<PaymentOrderItemEntity> {




	/**
	 * 交易状态改为申请退款中
	 * @param id
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value="update finance_pay_order_item set trade_status ='REFUND_APPLYING' where id = ?1 and trade_status = 'NORMAL'", nativeQuery=true)
	public Integer updateTradeStatusById(Long id);

	@Query(value = "select * from finance_pay_order_item where order_no = ?1 and pay_type = ?2 and (pay_status = ?3 or pay_status = '05')",nativeQuery = true)
	public PaymentOrderItemEntity findByOrderIdAndPayTypeAndPayStatus(
			String orderId, String payType, String payStatus);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_pay_order_item set pay_status = ?3,flow_number = ?4 where order_no = ?1 and pay_type = ?2",nativeQuery = true)
	public void updatePayStatusByOrderId(String orderId, String payType,
			String payStatus,String FlowNumber);

	/**
	 * 查询支付成功正常的订单明细的条数
	 * @version 2018年7月18日
	 * @param delete
	 * @param recordNo
	 * @param string
	 * @return
	 */
	@Query(value="select count(*) from finance_pay_order_item where status<> ?1 and order_no = ?2  and pay_status = '02' and trade_status = ?3",nativeQuery=true)
	public Integer findByStatusNotAndOrderNoAndNormal(int delete,
			String recordNo, String string);


	/**
	 * 根据订单编号查询已支付完成且融资单状态为未支付完全的数据
	 * @param status
	 * @param payStatus
	 * @return
	 */
	@Query(value="select pay.* from finance_pay_order_item pay inner join finance_cr_record record on "
			+ "record.record_no = pay.order_no where pay.status != ?1 and pay.pay_status = ?2 and record.credit_status in ('WAIT_PAY','SUMBIT_TO_BANK') and pay.trade_status = 'NORMAL'", nativeQuery=true)
	public List<PaymentOrderItemEntity> findPayOrderItemForInfo(int status,String payStatus);

	/**
	 * 查询线下支付待确认的支付明细
	 * @version 2018年7月20日
	 * @param recordNo
	 * @return
	 */ 
	@Query(value = "select * from finance_pay_order_item where order_no = ?1 and pay_type = 'OFFLINE_PAYMENT' and pay_status = '05' and trade_status = 'NORMAL' and status<>3",nativeQuery = true)
	public List<PaymentOrderItemEntity> findOfflineItemInfo(
			String recordNo);

	@Query(value = "select fpo.create_user,fpo.collect_enterprise_name,fpo.create_order_time,fpo.discount_amt,fpo.flow_number," +
		" fpo.order_amt,fpo.order_no,fpo.pay_enterprise_id,txp.enterprise_name,fpoi.pay_status,fpo.pay_time,fpo.pay_type,fpo.product_name," +
		" fpo.product_remark,fpo.service_no,fpo.trade_status,fpt.cr_prod_bank_name " +
		" from finance_pay_order_item fpoi" +
		" left join finance_cr_record fcr on fpoi.order_no = fcr.record_no" +
		" left join finance_product_type fpt on fpt.id = fcr.cr_prod_id" +
		" left join tb_xa_purchaser txp on txp.id = fcr.originator_enterprise_id" +
		" left join finance_pay_order fpo on fpo.order_no = fpoi.order_no" +
 		" where 1=1" +
		" and fpoi.status <>?1" +
		" and fpoi.service_no = ?2" +
		" and fpoi.order_no = ?3",nativeQuery = true)
	public List<Object[]> findAllByOrderNo1(int delete, String string,
			String record);

	/**
	 * 根据融资单号查询订单信息
	 * @param delete
	 * @param string
	 * @param record
	 * @return
	 */
	@Query(value = "select fpo.create_user,fpo.collect_enterprise_name,fpo.create_order_time,fpo.discount_amt,fpo.flow_number," +
			" fpo.order_amt,fpo.order_no,fpo.pay_enterprise_id,txs.enterprise_name,fpoi.pay_status,fpo.pay_time,fpo.pay_type,fpo.product_name," +
			" fpo.product_remark,fpo.service_no,fpo.trade_status,fpt.cr_prod_bank_name " +
			" from finance_pay_order_item fpoi" +
			" left join finance_cr_record fcr on fpo.order_no = fcr.record_no" +
			" left join finance_product_type fpt on fpt.id = fcr.cr_prod_id" +
			" left join tb_xa_supplier txs on txs.id = fcr.participator_enterprise_id" +
			" left join finance_pay_order fpo on fpo.order_no = fpoi.order_no" +
			" where 1=1" +
			" and fpoi.status <>?1" +
			" and fpoi.service_no = ?2" +
			" and fpoi.order_no = ?3",nativeQuery = true)
	public List<Object[]> findAllByOrderNo(int delete, String string,
			String record);
	





}
